﻿using DS_Gimnasio;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Runtime.Serialization;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using BS_Gimnasio.General;

namespace BS_Gimnasio.Seguridad
{
    public class cls_Respaldos : cls_Persona
    {
        #region Variables

        private int _idGimnasio;
        private string _servidorFTP;
        private string _usuarioFTP;
        private string _passwordFTP;
        private DateTime _fechaRespaldo;
        private DateTime _horaRespaldo;
        private string _cantidadTablas;
        private string _cantidadSP;
        private string _tamanoBD;
        private string _nombreBD;
        private string nombreArchivo;
        private string _rutaDescarga;
        private bool _lunes;
        private bool _martes;
        private bool _miercoles;
        private bool _jueves;
        private bool _viernes;
        private bool _sabado;
        private bool _domingo;
        private string _tipoAccion;

        #endregion

        #region Propiedades

        [DataMember()]
        public int IdGimnasio
        {
            get { return _idGimnasio; }
            set { _idGimnasio = value; }
        }

        [DataMember()]
        public string TipoAccion
        {
            get { return _tipoAccion; }
            set { _tipoAccion = value; }
        }

        [DataMember()]
        public bool Lunes
        {
            get { return _lunes; }
            set { _lunes = value; }
        }

        [DataMember()]
        public bool Martes
        {
            get { return _martes; }
            set { _martes = value; }
        }

        [DataMember()]
        public bool Miercoles
        {
            get { return _miercoles; }
            set { _miercoles = value; }
        }

        [DataMember()]
        public bool Jueves
        {
            get { return _jueves; }
            set { _jueves = value; }
        }

        [DataMember()]
        public bool Viernes
        {
            get { return _viernes; }
            set { _viernes = value; }
        }

        [DataMember()]
        public bool Sabado
        {
            get { return _sabado; }
            set { _sabado = value; }
        }

        [DataMember()]
        public bool Domingo
        {
            get { return _domingo; }
            set { _domingo = value; }
        }

        [DataMember()]
        public string NombreArchivo
        {
            get { return nombreArchivo; }
            set { nombreArchivo = value; }
        }

        [DataMember()]
        public string RutaDescarga
        {
            get { return _rutaDescarga; }
            set { _rutaDescarga = value; }
        }

        [DataMember()]
        public string NombreBD
        {
            get { return _nombreBD; }
            set { _nombreBD = value; }
        }

        [DataMember()]
        public string TamanoBD
        {
            get { return _tamanoBD; }
            set { _tamanoBD = value; }
        }

        [DataMember()]
        public string CantidadSP
        {
            get { return _cantidadSP; }
            set { _cantidadSP = value; }
        }

        [DataMember()]
        public string CantidadTablas
        {
            get { return _cantidadTablas; }
            set { _cantidadTablas = value; }
        }

        [DataMember()]
        public string PasswordFTP
        {
            get { return _passwordFTP; }
            set { _passwordFTP = value; }
        }

        [DataMember()]
        public DateTime FechaRespaldo
        {
            get { return _fechaRespaldo; }
            set { _fechaRespaldo = value; }
        }

        [DataMember()]
        public DateTime HoraRespaldo
        {
            get { return _horaRespaldo; }
            set { _horaRespaldo = value; }
        }

        [DataMember()]
        public string UsuarioFTP
        {
            get { return _usuarioFTP; }
            set { _usuarioFTP = value; }
        }

        [DataMember()]
        public string ServidorFTP
        {
            get { return _servidorFTP; }
            set { _servidorFTP = value; }
        }

        #endregion

        private void InsertarRegistro(ref string mensajeError)
        {
            if (TipoAccion == "BACKUP")
                crearRespaldo();
            else if (TipoAccion == "RESTORE")
                RestaurarBD();
        }

        private void ActualizarRegistro(ref string mensajeError)
        {
            if (TipoAccion == "ACTUALIZAR")
                actualizarDatos();
            else if (TipoAccion == "INSERTAR")
                insertarDatos();
        }

        private void insertarDatos()
        {
            using (TransactionScope transaction = new TransactionScope())
            {
                try
                {
                    using (dtacls_GimnasioDataContext db = new dtacls_GimnasioDataContext())
                        db.SP_Insertar_Parametros_Respaldos_FTP(IdUsuarioInicioSeccion, IdGimnasio, Lunes, Martes, Miercoles, Jueves, Viernes, Sabado, Domingo,
                            HoraRespaldo.TimeOfDay, ServidorFTP, UsuarioFTP, PasswordFTP, 1);
                    transaction.Complete();
                }

                catch (SqlException ex)
                {
                    NumeroError = ex.ErrorCode;
                    MensajeError = ex.Message;
                }

                catch (Exception e)
                {
                    MensajeError = e.Message;
                }
            }
        }

        private void actualizarDatos()
        {
            using (TransactionScope transaction = new TransactionScope())
            {
                try
                {
                    using (dtacls_GimnasioDataContext db = new dtacls_GimnasioDataContext())
                        db.SP_Actualizar_Parametros_Respaldos_FTP(IdUsuarioInicioSeccion, IdGimnasio, Lunes, Martes, Miercoles, Jueves, Viernes, Sabado, Domingo,
                            HoraRespaldo.TimeOfDay, ServidorFTP, UsuarioFTP, PasswordFTP, 1);
                    transaction.Complete();
                }

                catch (SqlException ex)
                {
                    NumeroError = ex.ErrorCode;
                    MensajeError = ex.Message;
                }

                catch (Exception e)
                {
                    MensajeError = e.Message;
                }
            }
        }


        public override cls_Base TraerObjeto()
        {
            try
            {
                using (dtacls_GimnasioDataContext db = new dtacls_GimnasioDataContext())
                {
                    ISingleResult<SP_Seleccionar_Filtro_Respaldos_FTPResult> resultado = db.SP_Seleccionar_Filtro_Respaldos_FTP(IdUsuarioInicioSeccion, IdGimnasio);
                    DataTable table = cls_Adaptador.ToDataTable(resultado.ToList());

                    if (table != null & table.Rows.Count > 0)
                    {
                        DataRow fila = table.Rows[0];
                        IdGimnasio = Convert.ToInt32(fila[1].ToString());
                        Lunes = Convert.ToBoolean(fila[2].ToString());
                        Martes = Convert.ToBoolean(fila[3].ToString());
                        Miercoles = Convert.ToBoolean(fila[4].ToString());
                        Jueves = Convert.ToBoolean(fila[5].ToString());
                        Viernes = Convert.ToBoolean(fila[6].ToString());
                        Sabado = Convert.ToBoolean(fila[7].ToString());
                        Domingo = Convert.ToBoolean(fila[8].ToString());
                        HoraRespaldo = Convert.ToDateTime(fila[9].ToString());
                        ServidorFTP = fila[10].ToString();
                        UsuarioFTP = fila[11].ToString();
                        PasswordFTP = fila[12].ToString();
                        CantidadTablas = fila[14].ToString();
                        CantidadSP = fila[15].ToString();
                        TamanoBD = fila[16].ToString();
                    }
                    else
                    {
                        MensajeError = "Configuracion existente";
                    }
                }
            }

            catch (SqlException ex)
            {
                NumeroError = ex.ErrorCode;
                MensajeError = ex.Message;
            }

            catch (Exception e)
            {
                MensajeError = e.Message;
            }

            return this;
        }

        public void crearRespaldo()
        {
            cls_AES claseAES = new cls_AES();
            string conStr = ConfigurationManager.AppSettings["conStr"];

            using (SqlConnection conn = new SqlConnection(conStr))
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                DateTimeFormatInfo dtinfo = new CultureInfo("es-ES", false).DateTimeFormat;

                //string fileName = "DBGimnasio" + "_" + FechaRespaldo.Year + "_" + dtinfo.GetMonthName(FechaRespaldo.Month) + "_" + FechaRespaldo.Day + "__" + FechaRespaldo.Hour + "_" + FechaRespaldo.Minute + "_" + FechaRespaldo.Second + ".bak";
                string temporaryTableName = findUniqueTemporaryTableName();
                string sql;

                sql = String.Format("BACKUP DATABASE {0} TO DISK = N'{1}\\{0}.bak' WITH FORMAT, COPY_ONLY, INIT, NAME = N'{0} - Full Database Backup', SKIP ", "DBGimnasio", @"C:\temp", "DBGimnasio");
                command.CommandText = sql;
                conn.Open();
                command.ExecuteNonQuery();

                sql = String.Format("IF OBJECT_ID('tempdb..##{0}') IS NOT NULL DROP TABLE ##{0}", temporaryTableName);
                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = String.Format("CREATE TABLE ##{0} (bck VARBINARY(MAX))", temporaryTableName);
                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = String.Format("INSERT INTO ##{0} SELECT bck.* FROM OPENROWSET(BULK '{1}\\{2}.bak',SINGLE_BLOB) bck", temporaryTableName, @"C:\temp", "DBGimnasio");
                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = String.Format("SELECT bck FROM ##{0}", temporaryTableName);

                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);

                DataRow dr = ds.Tables[0].Rows[0];
                byte[] backupFromServer = new byte[0];
                backupFromServer = (byte[])dr["bck"];
                int aSize = new int();
                aSize = backupFromServer.GetUpperBound(0) + 1;

                FileStream fs = new FileStream(String.Format("{0}\\{1}", @"C:\\temp", nombreArchivo), FileMode.OpenOrCreate, FileAccess.Write);
                fs.Write(backupFromServer, 0, aSize);
                fs.Close();

                sql = String.Format("DROP TABLE ##{0}", temporaryTableName);
                command.CommandText = sql;
                command.ExecuteNonQuery();
                conn.Close();

                byte[] _backup = FileToByteArray(@"C:\\temp\\" + nombreArchivo).DocContent;
                FtpRemoteUpload ftp = new FtpRemoteUpload(_backup, nombreArchivo, _rutaDescarga, _usuarioFTP, _passwordFTP);
                ftp.UploadFile();
            }
        }

        private Document FileToByteArray(string fileName)
        {
            byte[] fileContent = null;

            System.IO.FileStream fs = new System.IO.FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read);
            System.IO.BinaryReader binaryReader = new System.IO.BinaryReader(fs);
            long byteLength = new System.IO.FileInfo(fileName).Length;

            fileContent = binaryReader.ReadBytes((Int32)byteLength);
            fs.Close();
            fs.Dispose();
            binaryReader.Close();

            Document documento = new Document();
            documento.DocName = fileName;
            documento.DocContent = fileContent;

            return documento;
        }

        protected string findUniqueTemporaryTableName()
        {
            cls_AES claseAES = new cls_AES();

            string conStr = ConfigurationManager.AppSettings["conStr"];
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                string name = "afpTempBackup";
                int counter = 0;
                string sql = "";
                SqlCommand mycommand = new SqlCommand();
                mycommand.Connection = conn;

                while (true)
                {
                    ++counter;

                    sql = String.Format("SELECT OBJECT_ID('tempdb..##{0}') as xyz", name + counter.ToString());
                    mycommand.CommandText = sql;

                    conn.Open();

                    if (mycommand.ExecuteScalar().ToString() == "")
                    {
                        return name + counter.ToString();
                    }

                    conn.Close();
                }
            }
        }

        public bool validaExistenciaArchivoFtp()
        {
            try
            {
                bool existe = false;
                FtpWebRequest dirFtp = ((FtpWebRequest)FtpWebRequest.Create(ServidorFTP));

                NetworkCredential cr = new NetworkCredential(UsuarioFTP, PasswordFTP);
                dirFtp.Credentials = cr;
                dirFtp.Method = "LIST";
                dirFtp.Method = WebRequestMethods.Ftp.ListDirectory;
                StreamReader reader = new StreamReader(dirFtp.GetResponse().GetResponseStream());

                List<string> lista = new List<string>();

                string line = reader.ReadLine();

                while (!string.IsNullOrEmpty(line))
                {
                    lista.Add(line);
                    line = reader.ReadLine();
                    if (line == nombreArchivo)
                        existe = true;
                }

                return existe;
            }

            catch (WebException ex)
            {
                Console.Write(ex.Message);
                return (false);
            }
        }

        private void RestaurarBD()
        {
            string conStr = ConfigurationManager.AppSettings["conStr"];
            string rutaDescarga = @"C:\temp\Restore\";
            FtpRemoteDownload _descarga = new FtpRemoteDownload(RutaDescarga, rutaDescarga, _usuarioFTP, _passwordFTP);

            string mensajeRetorno = _descarga.DownloadFile();

            string sUser = @"USE [master]
                                ALTER DATABASE [DBGimnasio] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";

            string sBackup = " RESTORE DATABASE " + "DBGimnasio" +
                             " FROM DISK = '" + @"C:\temp\Restore\" + nombreArchivo + "'" +
                             " WITH REPLACE";

            using (SqlConnection con = new SqlConnection(conStr))
            {
                try
                {
                    con.Open();

                    SqlCommand cmdBackUp = new SqlCommand(sUser, con);
                    cmdBackUp.ExecuteNonQuery();
                    cmdBackUp = new SqlCommand(sBackup, con);
                    cmdBackUp.ExecuteNonQuery();

                    Console.Write("Se ha restaurado la copia de la base de datos.");
                    con.Close();
                }

                catch (Exception ex)
                {
                    Console.Write(ex.Message);

                    con.Close();
                    con.Open();
                    sBackup = @"USE [master]
                                ALTER DATABASE [DBGimnasio] SET MULTI_USER WITH ROLLBACK IMMEDIATE";

                    SqlCommand cmdBackUp = new SqlCommand(sBackup, con);
                    cmdBackUp.ExecuteNonQuery();

                    Console.Write("ERROR restaurado la copia de la base de datos.");
                    con.Close();
                }
            }
        }

    }
}
